﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class BanVeChuyenBayDAO
    {
        public int Banve(BanVeChuyenBayDTO banve)
        {
            String sql = string.Format(" insert into VeChuyenBay(MaChuyenBay,MaHangVe,MaHanhKhach,GiaTien) values ('{0}','{1}','{2}','{3}')", banve.MaChuyenBay, banve.MaHangVe, banve.MaHanhKhach, banve.GiaTien);
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            return res;
        }
        public List<BanVeChuyenBayDTO> loadvecb()
        {


            String sql = "SELECT vcb.MaChuyenBay,hv.TenHangVe,hk.TenHanhKhach,vcb.MaHanhKhach from VeChuyenBay vcb,HangVe hv,HanhKhach hk where vcb.MaHangVe=hv.MaHangVe and hk.MaHanhKhach=vcb.MaHanhKhach";

            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<BanVeChuyenBayDTO> ds = new List<BanVeChuyenBayDTO>();
           
            if (reader != null)
            {
                while (reader.Read())
                {

                    BanVeChuyenBayDTO banve = new BanVeChuyenBayDTO();
                    HangVeDTO hve = new HangVeDTO();
                    HanhKhachDTO hk = new HanhKhachDTO();
                    banve.MaChuyenBay = reader.GetString(0);
                    banve.MaHanhKhach = reader.GetString(3);
                    banve.HangVe = hve;
                    hve.TenHangVe = reader.GetString(1);
                    banve.HanhKhach = hk;
                    hk.TenHK = reader.GetString(2);
                    ds.Add(banve);
                }
            }
            conn.Close();
            return ds;

        }
    }
}